package zy.dao.base.shop.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.shop.ShopDAO;
import zy.dto.base.shop.ShopMoneyDetailsDto;
import zy.entity.base.depot.T_Base_Depot;
import zy.entity.base.shop.T_Base_Shop;
import zy.entity.sell.set.T_Sell_Set;
import zy.entity.sys.user.T_Sys_User;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.MD5;
import zy.util.StringUtil;

@Repository
public class ShopDAOImpl extends BaseDaoImpl implements ShopDAO{

	@Override
	public List<T_Base_Shop> all_list(Map<String, Object> param) {
		Object shop_type = param.get("shop_type");
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,sp_shop_type,sp_state,sp_end,sp_upcode,companyid");
		sql.append(" FROM t_base_shop t");
		sql.append(" WHERE 1 = 1");
        if(null != shop_type && !"".equals(shop_type)){
        	sql.append(" AND sp_shop_type = :shop_type");
        }
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}
	
	@Override
	public List<T_Base_Shop> sub_list(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object searchContent = param.get("searchContent");
		Object sp_code = param.get("sp_code");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,sp_shop_type,sp_state,sp_end,sp_upcode,companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" ,ty_name");
		}else {
			sql.append(" ,(SELECT ty_name FROM common_type WHERE ty_id = sp_shop_type LIMIT 1) AS ty_name");
		}
		sql.append(" FROM t_base_shop t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(sp_code)){
			sql.append(" AND sp_code = :sp_code");
		}
		if(StringUtil.isNotEmpty(param.get("shopCodes"))){
			sql.append(" AND sp_code IN (:shopCodes)");
		}
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}
	
	@Override
	public List<T_Base_Shop> sub_list_jmd(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,sp_shop_type,sp_state,sp_end,sp_upcode,companyid");
		sql.append(" ,(SELECT ty_name FROM common_type WHERE ty_id = sp_shop_type LIMIT 1) AS ty_name");
		sql.append(" FROM t_base_shop t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND sp_upcode=:shop_code AND sp_shop_type = "+CommonUtil.FOUR);
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}

	@Override
	public List<T_Base_Shop> selectList(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object searchContent = param.get("searchContent");
		Object sp_code = param.get("sp_code");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,sp_shop_type,sp_state,sp_end,sp_upcode,companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" ,ty_name");
		}else {
			sql.append(" ,(SELECT ty_name FROM common_type WHERE ty_id = sp_shop_type LIMIT 1) AS ty_name");
		}
		sql.append(" FROM t_base_shop t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(sp_code)){
			param.put("shop_codes", StringUtil.parseList(StringUtil.trimString(sp_code)));
			sql.append(" AND sp_code IN (:shop_codes)");
		}
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}

	@Override
	public List<T_Base_Shop> price_list(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object sp_shop_type = param.get("sp_shop_type");
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,sp_shop_type,sp_state,sp_end,sp_upcode,ty_name,companyid");
		sql.append(" FROM t_base_shop t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
        if(null != sp_shop_type && !"".equals(sp_shop_type)){
        	sql.append(" AND sp_shop_type = :sp_shop_type");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}
	
	@Override
	public List<T_Base_Shop> want_list(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,sp_shop_type,sp_state,sp_end,sp_upcode,companyid,");
		sql.append(" (SELECT ty_name FROM common_type WHERE ty_id = sp_shop_type LIMIT 1) AS ty_name");
		sql.append(" FROM t_base_shop t");
		sql.append(" WHERE 1 = 1");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司(查询下级自营店铺)
			sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.THREE);
		}else{
			sql.append(" AND sp_code = :shop_code");
		}
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}
	
	@Override
	public List<T_Base_Shop> allot_list(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,sp_shop_type,sp_rate,sp_state,sp_end,sp_upcode,companyid,");
		sql.append(" IFNULL(sp_receivable,0) AS sp_receivable,IFNULL(sp_received,0) AS sp_received,IFNULL(sp_prepay,0) AS sp_prepay,");
		sql.append(" (SELECT ty_name FROM common_type WHERE ty_id = sp_shop_type LIMIT 1) AS ty_name");
		sql.append(" FROM t_base_shop t");
		sql.append(" WHERE 1 = 1");
		if(CommonUtil.ONE.equals(shop_type)){//总公司(查询下级分公司、加盟店、合伙店)
			sql.append(" AND ((sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.FOUR+" OR sp_shop_type = "+CommonUtil.FIVE+"))");
			sql.append(" OR sp_shop_type = "+CommonUtil.TWO+")");
		}else if(CommonUtil.TWO.equals(shop_type)){//分公司(查询下级加盟店、合伙店)
			sql.append(" AND sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.FOUR+" OR sp_shop_type = "+CommonUtil.FIVE+")");
		}else{
			sql.append(" AND sp_code = :shop_code");
		}
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}
	
	@Override
	public List<T_Base_Shop> money_list(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,ty_name,sp_shop_type,sp_rate,sp_state,sp_end,sp_upcode,t.companyid,spi_man,spi_mobile,");
		sql.append(" sp_init_debt,sp_receivable,sp_received,sp_prepay");
		sql.append(" FROM t_base_shop t");
		sql.append(" JOIN common_type ON ty_id=sp_shop_type");
		sql.append(" JOIN t_base_shop_info si ON si.spi_shop_code=t.sp_code AND si.companyid=t.companyid");
		sql.append(" WHERE 1 = 1");
		if(CommonUtil.ONE.equals(shop_type)){//总公司(查询下级分公司、加盟店、合伙店)
			sql.append(" AND ((sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.FOUR+" OR sp_shop_type = "+CommonUtil.FIVE+"))");
			sql.append(" OR sp_shop_type = "+CommonUtil.TWO+")");
		}else if(CommonUtil.TWO.equals(shop_type)){//分公司(查询下级加盟店、合伙店)
			sql.append(" AND sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.FOUR+" OR sp_shop_type = "+CommonUtil.FIVE+")");
		}else{
			sql.append(" AND sp_code = :shop_code");
		}
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}
	
	@Override
	public List<T_Base_Shop> sub_tree(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,sp_shop_type,sp_state,sp_end,sp_upcode,companyid");
		sql.append(" FROM t_base_shop t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}
	
	@Override
	public List<T_Base_Shop> up_sub_list(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,sp_shop_type,sp_state,sp_end,sp_upcode,ty_name,companyid");
		sql.append(" FROM t_base_shop t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.sp_code,:searchContent)>0 OR INSTR(t.sp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sp_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}

	@Override
	public Integer count(Map<String, Object> param) {
		Object name = param.get("name");
		Object ty_id = param.get("ty_id");
		Object shop_type = param.get("shop_type");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_base_shop s");
		sql.append(" where 1=1");
		if(null != name && !"".equals(name)){
			sql.append(" and instr(sp_name,:name)>0");
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(" and s.sp_upcode=:shop_code");
		}else{
			sql.append(" and s.sp_code=:shop_code");
		}
		if(null != ty_id && !"".equals(ty_id)){
			sql.append(" and sp_shop_type=:ty_id");
		}
		sql.append(" and companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Base_Shop> list(Map<String, Object> param) {
		Object name = param.get("name");
		Object ty_id = param.get("ty_id");
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select sp_id,sp_code,sp_name,ty_name,sp_state,sp_end,spi_man,spi_mobile,");
		sql.append(" sp_lock,sp_shop_type,");
		sql.append(" (SELECT COUNT(1) FROM t_base_shop_reward sr WHERE sr_sp_code = sp_code AND sr.companyid = s.companyid) AS reward_count");
		sql.append(" from t_base_shop s");
		sql.append(" join common_type");
		sql.append(" on ty_id=sp_shop_type");
		sql.append(" join t_base_shop_info si");
		sql.append(" on si.spi_shop_code=s.sp_code");
		sql.append(" and si.companyid=s.companyid");
		sql.append(" where 1=1");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(" and s.sp_upcode=:shop_code");
		}else{
			sql.append(" and s.sp_code=:shop_code");
		}
		if(null != name && !"".equals(name)){
			sql.append(" and instr(sp_name,:name)>0");
		}
		if(null != ty_id && !"".equals(ty_id)){
			sql.append(" and sp_shop_type=:ty_id");
		}
		sql.append(" and s.companyid=:companyid");
		sql.append(" order by sp_id desc");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}

	
	@Override
	public Integer countZone(Map<String, Object> param) {
		Object name = param.get("name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_base_shop s");
		sql.append(" where 1=1");
		if(null != name && !"".equals(name)){
			sql.append(" and instr(sp_spell,:name)>0");
		}
		sql.append(" and sp_shop_type=:shop_type");
		sql.append(" and companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Base_Shop> listZone(Map<String, Object> param) {
		Object name = param.get("name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select sp_id,sp_code,sp_name,ty_name,sp_state,sp_end,spi_man,spi_mobile,sp_shop_type");
		sql.append(" from t_base_shop s");
		sql.append(" join common_type");
		sql.append(" on ty_id=sp_shop_type");
		sql.append(" join t_base_shop_info si");
		sql.append(" on si.spi_shop_code=s.sp_code");
		sql.append(" and si.companyid=s.companyid");
		sql.append(" where 1=1");
		if(null != name && !"".equals(name)){
			sql.append(" and instr(sp_spell,:name)>0");
		}
		sql.append(" and sp_shop_type=:shop_type");
		sql.append(" and s.companyid=:companyid");
		sql.append(" order by sp_id desc");
		
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}

	@Override
	public List<T_Base_Shop> listByEmp(Map<String, Object> param) {
		Object shop_type = param.get("shop_type");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select sp_id,sp_code,sp_name");
		sql.append(" from t_base_shop s");
		sql.append(" where 1=1 ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(" and s.sp_upcode=:shop_code");
		}else{
			sql.append(" and s.sp_code=:shop_code");
		}
		sql.append(" and s.companyid=:companyid");
		sql.append(" order by sp_id desc");
		
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
	}


	@Override
	public T_Base_Shop queryByID(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ");
		sql.append(" sp_id,sp_code,sp_name,sp_spell,sp_shop_type,");
		sql.append(" sp_state,sp_end,sp_lock,sp_upcode,sp_init,");
		sql.append(" sp_main,sp_init_debt,sp_receivable,sp_received,sp_prepay");
		sql.append(" ,sp_sort_cycle,sp_settle_cycle,sp_rate,");
		sql.append(" spi_id,spi_man,spi_tel,spi_mobile,spi_addr,");
		sql.append(" spi_province,spi_city,spi_town,spi_remark,ty_name");
		sql.append(" from t_base_shop s");
		sql.append(" join t_base_shop_info si");
		sql.append(" on si.spi_shop_code=s.sp_code");
		sql.append(" and si.companyid=s.companyid");
		sql.append(" join common_type t");
		sql.append(" on ty_id=s.sp_shop_type");
		sql.append(" where s.sp_id=:sp_id");
		try{
			T_Base_Shop data = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("sp_id", id),new BeanPropertyRowMapper<>(T_Base_Shop.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	@Override
	public T_Base_Shop load(String sp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ");
		sql.append(" sp_id,sp_code,sp_name,sp_spell,sp_shop_type,");
		sql.append(" sp_state,sp_end,sp_lock,sp_upcode,sp_init,");
		sql.append(" sp_main,sp_init_debt,sp_receivable,sp_received,sp_prepay");
		sql.append(" ,sp_sort_cycle,sp_settle_cycle,sp_rate,");
		sql.append(" spi_id,spi_man,spi_tel,spi_mobile,spi_addr,");
		sql.append(" spi_province,spi_city,spi_town,spi_remark,ty_name");
		sql.append(" from t_base_shop s");
		sql.append(" join t_base_shop_info si");
		sql.append(" on si.spi_shop_code=s.sp_code");
		sql.append(" and si.companyid=s.companyid");
		sql.append(" join common_type t");
		sql.append(" on ty_id=s.sp_shop_type");
		sql.append(" where s.sp_code=:sp_code and s.companyid = :companyid");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Shop.class));
		}catch(Exception e){
			return null;
		}
	}
	/**
	 * 更新店铺信息
	 * 1.更新主表信息，如果帐套已经启动，则不能更新期初欠款等金额字段
	 * 2.更新子表信息
	 * */
	@Override
	public void update(T_Base_Shop model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_shop SET");
		sql.append(" sp_name=:sp_name,sp_spell=:sp_spell,");
		sql.append(" sp_rate=:sp_rate,sp_main=:sp_main,");
		if(null != model.getSp_init_debt()){
			sql.append(" sp_init_debt=:sp_init_debt,");
		}
		sql.append(" sp_sort_cycle=:sp_sort_cycle,sp_settle_cycle=:sp_settle_cycle");
		sql.append(" WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
		sql.setLength(0);
		sql.append(" UPDATE t_base_shop_info SET");
		sql.append(" spi_man=:spi_man,spi_tel=:spi_tel,spi_mobile=:spi_mobile,spi_addr=:spi_addr,");
		sql.append(" spi_province=:spi_province,spi_city=:spi_city,spi_town=:spi_town,spi_remark=:spi_remark");
		sql.append(" WHERE spi_id=:spi_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}
	/**
	 * 保存店铺信息
	 * 1.查询编号
	 * 2.保存主表：期初欠款=实际欠款=应收金额
	 * 3.保存子表信息
	 * 4.保存仓库信息
	 * 5.判断类型：分公司或加盟店
	 * 6.分公司、加盟店：保存管理员用户
	 * 7.自营店、合伙店、加盟店：保存前台收银设置
	 * */
	@Override
	public void save(T_Base_Shop model) {
		StringBuffer sql = new StringBuffer("");
		/**
		 * 保存店铺数据
		 * */
		sql.append(" select f_three_code(max(sp_code+0)) from t_base_shop ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String sp_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		model.setSp_code(sp_code);
		sql.setLength(0);
		sql.append("INSERT INTO t_base_shop (");
		sql.append("sp_code,sp_name,sp_spell,sp_shop_type,sp_main,");
		sql.append("sp_state,sp_end,sp_upcode,sp_init,sp_rate,");
		sql.append("sp_init_debt,sp_receivable,");
		sql.append("sp_sort_cycle,sp_settle_cycle,companyid");
		sql.append(" )VALUES( ");
		sql.append(":sp_code,:sp_name,:sp_spell,:sp_shop_type,:sp_main,");
		sql.append(":sp_state,:sp_end,:sp_upcode,:sp_init,:sp_rate,");
		sql.append(":sp_init_debt,:sp_init_debt,");
		sql.append(":sp_sort_cycle,:sp_settle_cycle,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
		sql.setLength(0);
		sql.append("INSERT INTO t_base_shop_info (");
		sql.append("spi_man,spi_tel,spi_mobile,spi_addr,");
		sql.append("spi_province,spi_city,spi_town,spi_remark,");
		sql.append("spi_shop_code,companyid");
		sql.append(")VALUES(");
		sql.append(":spi_man,:spi_tel,:spi_mobile,:spi_addr,");
		sql.append(":spi_province,:spi_city,:spi_town,:spi_remark,");
		sql.append(":sp_code,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
		
		/***
		 * 保存仓库数据
		 * */
		sql.setLength(0);
		sql.append(" select f_three_code(max(dp_code+0)) from t_base_depot ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String dp_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		T_Base_Depot depot = new T_Base_Depot();
		depot.setCompanyid(model.getCompanyid());
		depot.setDp_code(dp_code);
		depot.setDp_addr(model.getSpi_addr());
		depot.setDp_default(1);
		depot.setDp_state(0);
		depot.setDp_man(model.getSpi_man());
		depot.setDp_mobile(model.getSpi_mobile());
		depot.setDp_name(model.getSp_name()+"仓库");
		depot.setDp_shop_code(model.getSp_code());
		depot.setDp_spell(StringUtil.getSpell(depot.getDp_name()));
		sql.setLength(0);
		sql.append("INSERT INTO t_base_depot (");
		sql.append("dp_code,dp_name,dp_spell,companyid,");
		sql.append("dp_tel,dp_man,dp_state,dp_addr,");
		sql.append("dp_default,dp_mobile,dp_shop_code");
		sql.append(")VALUES(");
		sql.append(":dp_code,:dp_name,:dp_spell,:companyid,");
		sql.append(":dp_tel,:dp_man,:dp_state,:dp_addr,");
		sql.append(":dp_default,:dp_mobile,:dp_shop_code");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(depot));
		/**
		 * 加盟店要根据角色添加用户
		 * */
		if(CommonUtil.FOUR.equals(model.getSp_shop_type())){
			sql.setLength(0);
			sql.append("select ro_code");
			sql.append(" from t_sys_role r");
			sql.append(" where ro_shop_type=:sp_shop_type");
			sql.append(" and r.companyid=:companyid");
			sql.append(" limit 1");
			String ro_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
			/*
			 * 注册帐号时新增角色菜单，店铺添加使用就行了，不用加
			 * sql.setLength(0);
			sql.append("select '"+ro_code+"' as rm_ro_code,mn_code as rm_mn_code,mn_state as rm_state,");
			if(CommonUtil.TWO.equals(model.getSp_shop_type())){
				sql.append("mn_area_limit as rm_limit,");
			}else{
				sql.append("mn_join_limit as rm_limit,");
			}
			sql.append(user.getCompanyid()+" AS companyid");
			sql.append(" from t_sys_menu");
			sql.append(" where 1=1");
			sql.append(" and instr(mn_shop_type,:shop_type)>0");
			sql.append(" and instr(mn_version,:version)>0");
			sql.append(" and mn_state=0");
			Map<String,Object> param = new HashMap<String, Object>(2);
			param.put("shop_type", model.getSp_shop_type());
			param.put("version", user.getCo_ver());
			List<T_Sys_RoleMenu> list = namedParameterJdbcTemplate.query(sql.toString()
					, param
					, new BeanPropertyRowMapper<>(T_Sys_RoleMenu.class));
			
			sql.setLength(0);
			sql.append(" INSERT INTO T_Sys_RoleMenu(");
			sql.append(" rm_ro_code,rm_mn_code,rm_state,rm_limit,companyid");
			sql.append(" )VALUES(");
			sql.append(" :rm_ro_code,:rm_mn_code,:rm_state,:rm_limit,:companyid");
			sql.append(")");
			
			namedParameterJdbcTemplate.batchUpdate(sql.toString(),SqlParameterSourceUtils.createBatch(list.toArray()));*/
			
			sql.setLength(0);
			sql.append(" select f_three_code(max(us_code+0)) from t_sys_user ");
			sql.append(" where 1=1");
			sql.append(" and companyid=:companyid");
			String us_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
			T_Sys_User add_user = new T_Sys_User(); 
			add_user.setUs_code(us_code);
			add_user.setUs_account(us_code);
			add_user.setUs_name("管理员");
			add_user.setUs_pass(MD5.encryptMd5(CommonUtil.INIT_PWD));
			add_user.setUs_date(DateUtil.getCurrentTime());
			add_user.setUs_default(1);
			add_user.setUs_ro_code(ro_code);
			add_user.setUs_shop_code(sp_code);
			add_user.setUs_limit(CommonUtil.PRICE_LIMIT);
			add_user.setCompanyid(model.getCompanyid());
			sql.setLength(0);
			sql.append("INSERT INTO T_Sys_User");
			sql.append(" (us_code,us_account,us_name,us_pass,us_state,us_date,");
			sql.append(" us_default,us_ro_code,us_shop_code,us_limit,companyid)");
			sql.append(" VALUES");
			sql.append(" (:us_code,:us_account,:us_name,:us_pass,:us_state,:us_date,");
			sql.append(" :us_default,:us_ro_code,:us_shop_code,:us_limit,:companyid)");
			namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(add_user));
		}
		
	}
	@Override
	public void saveSellSet(T_Base_Shop model) {
		/**
		 * 自营店、加盟店、合伙店要保存零售设置表，保证前台收银
		 * */
		if(CommonUtil.THREE.equals(model.getSp_shop_type()) 
				|| CommonUtil.FOUR.equals(model.getSp_shop_type())
				|| CommonUtil.FIVE.equals(model.getSp_shop_type())){
			StringBuffer sql = new StringBuffer("");
			sql.append("SELECT ss_key as st_key,ss_value as st_value,");
			sql.append(model.getCompanyid() + " as companyid,");
			sql.append("'"+model.getSp_code() + "' as st_shop_code");
			sql.append(" FROM common_sellset");
			sql.append(" WHERE ss_type='"+CommonUtil.KEY_SHOP+"'");
			
			List<T_Sell_Set> list = null;
			try {
				list = namedParameterJdbcTemplate.query(sql.toString()
						,new BeanPropertySqlParameterSource(model)
						,new BeanPropertyRowMapper<>(T_Sell_Set.class));
				sql.setLength(0);
				sql.append(" INSERT INTO t_sell_set (");
				sql.append(" st_key,st_value,st_shop_code,companyid");
				sql.append(" ) VALUES (");
				sql.append(" :st_key,:st_value,:st_shop_code,:companyid");
				sql.append(")");
				namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(list.toArray()));
			} catch (Exception e) {
			}
			
			/*sql.setLength(0);
			sql.append(" select f_three_code(max(sp_code+0)) from t_sell_print ");
			sql.append(" where 1=1");
			sql.append(" and companyid=:companyid");
			String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
			sql.setLength(0);
			sql.append(" INSERT INTO t_sell_print(");
			sql.append(" sp_code,sp_print,sp_auto,sp_shop_code,companyid");
			sql.append(" )VALUES(");
			sql.append(" '"+code+"',1,1,:sp_code,:companyid");
			sql.append(")");
			namedParameterJdbcTemplate.update(sql.toString(), 
					new BeanPropertySqlParameterSource(model));*/
		}
	}

	/**
	 * 保存分公司信息
	 * 1.查询编号
	 * 2.保存主表：期初欠款=实际欠款=应收金额
	 * 3.保存子表信息
	 * 4.保存仓库信息
	 * 5.判断类型：分公司或加盟店
	 * 6.分公司、加盟店：保存管理员用户
	 */
	@Override
	public void saveZone(T_Base_Shop model) {
		StringBuffer sql = new StringBuffer("");
		/**
		 * 保存店铺数据
		 * */
		sql.append(" select f_three_code(max(sp_code+0)) from t_base_shop ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String sp_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		model.setSp_code(sp_code);
		sql.setLength(0);
		sql.append("INSERT INTO t_base_shop (");
		sql.append("sp_code,sp_name,sp_spell,sp_shop_type,sp_main,");
		sql.append("sp_state,sp_end,sp_upcode,sp_init,sp_rate,");
		sql.append("sp_init_debt,sp_receivable,");
		sql.append("sp_sort_cycle,sp_settle_cycle,companyid");
		sql.append(" )VALUES( ");
		sql.append(":sp_code,:sp_name,:sp_spell,:sp_shop_type,:sp_main,");
		sql.append(":sp_state,:sp_end,:sp_code,:sp_init,:sp_rate,");
		sql.append(":sp_init_debt,:sp_init_debt,");
		sql.append(":sp_sort_cycle,:sp_settle_cycle,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
		sql.setLength(0);
		sql.append("INSERT INTO t_base_shop_info (");
		sql.append("spi_man,spi_tel,spi_mobile,spi_addr,");
		sql.append("spi_province,spi_city,spi_town,spi_remark,");
		sql.append("spi_shop_code,companyid");
		sql.append(")VALUES(");
		sql.append(":spi_man,:spi_tel,:spi_mobile,:spi_addr,");
		sql.append(":spi_province,:spi_city,:spi_town,:spi_remark,");
		sql.append(":sp_code,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
		
		/***
		 * 保存仓库数据
		 * */
		sql.setLength(0);
		sql.append(" select f_three_code(max(dp_code+0)) from t_base_depot ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String dp_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		T_Base_Depot depot = new T_Base_Depot();
		depot.setCompanyid(model.getCompanyid());
		depot.setDp_code(dp_code);
		depot.setDp_addr(model.getSpi_addr());
		depot.setDp_default(1);
		depot.setDp_state(0);
		depot.setDp_man(model.getSpi_man());
		depot.setDp_mobile(model.getSpi_mobile());
		depot.setDp_name(model.getSp_name()+"仓库");
		depot.setDp_shop_code(model.getSp_code());
		depot.setDp_spell(StringUtil.getSpell(depot.getDp_name()));
		sql.setLength(0);
		sql.append("INSERT INTO t_base_depot (");
		sql.append("dp_code,dp_name,dp_spell,companyid,");
		sql.append("dp_tel,dp_man,dp_state,dp_addr,");
		sql.append("dp_default,dp_mobile,dp_shop_code");
		sql.append(")VALUES(");
		sql.append(":dp_code,:dp_name,:dp_spell,:companyid,");
		sql.append(":dp_tel,:dp_man,:dp_state,:dp_addr,");
		sql.append(":dp_default,:dp_mobile,:dp_shop_code");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(depot));
		/**
		 * 分公司要根据角色添加用户
		 * */
		sql.setLength(0);
		sql.append("select ro_code");
		sql.append(" from t_sys_role r");
		sql.append(" where ro_shop_type=:sp_shop_type");
		sql.append(" and r.companyid=:companyid");
		sql.append(" limit 1");
		String ro_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		
		sql.setLength(0);
		sql.append(" select f_three_code(max(us_code+0)) from t_sys_user ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String us_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		T_Sys_User add_user = new T_Sys_User(); 
		add_user.setUs_code(us_code);
		add_user.setUs_account(us_code);
		add_user.setUs_name("管理员");
		add_user.setUs_pass(MD5.encryptMd5(CommonUtil.INIT_PWD));
		add_user.setUs_date(DateUtil.getCurrentTime());
		add_user.setUs_default(1);
		add_user.setUs_ro_code(ro_code);
		add_user.setUs_shop_code(sp_code);
		add_user.setUs_limit(CommonUtil.PRICE_LIMIT);
		add_user.setCompanyid(model.getCompanyid());
		sql.setLength(0);
		sql.append("INSERT INTO T_Sys_User");
		sql.append(" (us_code,us_account,us_name,us_pass,us_state,us_date,");
		sql.append(" us_default,us_ro_code,us_shop_code,us_limit,companyid)");
		sql.append(" VALUES");
		sql.append(" (:us_code,:us_account,:us_name,:us_pass,:us_state,:us_date,");
		sql.append(" :us_default,:us_ro_code,:us_shop_code,:us_limit,:companyid)");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(add_user));
	}

	@Override
	public void del(Integer id) {
		
	}

	@Override
	public void updateState(T_Base_Shop model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_shop");
		sql.append(" SET sp_state=:sp_state");
		sql.append(" WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public T_Base_Shop loadShop(String sp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT sp_id,sp_code,");
		sql.append(" IFNULL(sp_receivable,0) AS sp_receivable,IFNULL(sp_received,0) AS sp_received,IFNULL(sp_prepay,0) AS sp_prepay");
		sql.append(" FROM t_base_shop");
		sql.append(" WHERE sp_code = :sp_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Shop.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void updateReceivable(T_Base_Shop shop) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_base_shop SET sp_receivable=:sp_receivable WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(shop));
	}
	
	@Override
	public void updatePrepay(T_Base_Shop shop) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_base_shop SET sp_prepay=:sp_prepay WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(shop));
	}
	
	@Override
	public void updateSettle(T_Base_Shop shop) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_base_shop ");
		sql.append(" SET sp_receivable=:sp_receivable ");
		sql.append(" ,sp_received=:sp_received");
		sql.append(" ,sp_prepay=:sp_prepay");
		sql.append(" WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(shop));
	}
	
	private String getMoneyDetailsSQL(Map<String, Object> params,String type){
		if (StringUtil.isNotEmpty(params.get("type"))) {
			params.put("types", Arrays.asList(params.get("type").toString().split(",")));
		}
		if (StringUtil.isNotEmpty(params.get("pay_state"))) {
			params.put("pay_states", Arrays.asList(params.get("pay_state").toString().split(",")));
		}
		StringBuffer sql = new StringBuffer();
		if("allot".equals(type)){
			if(StringUtil.isNotEmpty(params.get("shopcode"))){
				sql.append(" AND at_shop_code = :shopcode ");
			}
			if(StringUtil.isNotEmpty(params.get("manager"))){
				sql.append(" AND at_manager = :manager ");
			}
			if(StringUtil.isNotEmpty(params.get("begindate"))){
				sql.append(" AND at_date >= :begindate ");
			}
			if(StringUtil.isNotEmpty(params.get("enddate"))){
				sql.append(" AND at_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(params.get("number"))) {
				sql.append(" AND INSTR(at_number,:number) > 0 ");
			}
			if (StringUtil.isNotEmpty(params.get("type"))) {
				sql.append(" AND at_type IN (:types) ");
			}
			if (StringUtil.isNotEmpty(params.get("pay_state"))) {
				sql.append(" AND at_pay_state IN (:pay_states) ");
			}
//			sql.append(" AND se_ar_state = 1");
		}else if("fee".equals(type)){
			if(StringUtil.isNotEmpty(params.get("shopcode"))){
				sql.append(" AND fe_shop_code = :shopcode ");
			}
			if(StringUtil.isNotEmpty(params.get("manager"))){
				sql.append(" AND fe_manager = :manager ");
			}
			if(StringUtil.isNotEmpty(params.get("begindate"))){
				sql.append(" AND fe_date >= :begindate ");
			}
			if(StringUtil.isNotEmpty(params.get("enddate"))){
				sql.append(" AND fe_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(params.get("number"))) {
				sql.append(" AND INSTR(fe_number,:number) > 0 ");
			}
			if (StringUtil.isNotEmpty(params.get("type"))) {
				sql.append(" AND 3 IN (:types) ");
			}
			if (StringUtil.isNotEmpty(params.get("pay_state"))) {
				sql.append(" AND fe_pay_state IN (:pay_states) ");
			}
			sql.append(" AND fe_ar_state = 1");
		}else if("prepay".equals(type)){
			if(StringUtil.isNotEmpty(params.get("shopcode"))){
				sql.append(" AND pp_shop_code = :shopcode ");
			}
			if(StringUtil.isNotEmpty(params.get("manager"))){
				sql.append(" AND pp_manager = :manager ");
			}
			if(StringUtil.isNotEmpty(params.get("begindate"))){
				sql.append(" AND pp_date >= :begindate ");
			}
			if(StringUtil.isNotEmpty(params.get("enddate"))){
				sql.append(" AND pp_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(params.get("number"))) {
				sql.append(" AND INSTR(pp_number,:number) > 0 ");
			}
			if (StringUtil.isNotEmpty(params.get("type"))) {
				String _type = StringUtil.trimString(params.get("type"));
				if(_type.contains("4") && _type.contains("5")){
				}else if(_type.contains("4")){
					sql.append(" AND pp_money > 0");
				}else if(_type.contains("5")){
					sql.append(" AND pp_money < 0");
				}else{
	        		sql.append(" AND 1=2 ");
	        	}
			}
			if (StringUtil.isNotEmpty(params.get("pay_state"))) {
				String _pay_state = StringUtil.trimString(params.get("pay_state"));
				if(!_pay_state.contains("2")){
					sql.append(" AND 1=2 ");
				}
			}
			sql.append(" AND pp_ar_state = 1");
		}
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}

	@Override
	public Map<String, Object> countsumMoneyDetails(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" COUNT(1) AS totalCount,");
		sql.append(" SUM(money) AS money,");
		sql.append(" SUM(discount_money) AS discount_money,");
		sql.append(" SUM(prepay) AS prepay,");
		sql.append(" SUM(received) AS received,");
		sql.append(" SUM(receivable) AS receivable");
		sql.append(" FROM (");
		sql.append(" SELECT at_sendmoney AS money,at_discount_money AS discount_money,");
		sql.append(" at_prepay AS prepay,at_received AS received,at_receivable AS receivable");
		sql.append(" FROM t_sort_allot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "allot"));
		sql.append(" UNION ALL");
		sql.append(" SELECT fe_money AS money,fe_discount_money AS discount_money,");
		sql.append(" fe_prepay AS prepay,fe_received AS received,fe_receivable AS receivable");
		sql.append(" FROM t_sort_fee t");
		sql.append(" JOIN t_base_shop sp ON sp_code = fe_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "fee"));
		sql.append(" UNION ALL");
		sql.append(" SELECT 0 AS money,0 AS discount_money,0 AS prepay,pp_money AS received,0 AS receivable");
		sql.append(" FROM t_sort_prepay t");
		sql.append(" JOIN t_base_shop sp ON sp_code = pp_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "prepay"));
		sql.append(" )temp");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<ShopMoneyDetailsDto> listMoneyDetails(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM (");
		sql.append(" SELECT at_id AS id,at_date AS make_date,at_number AS number,at_type AS type,at_shop_code AS shop_code,");
		sql.append(" at_pay_state AS pay_state,at_sendmoney AS money,at_discount_money AS discount_money,");
		sql.append(" at_prepay AS prepay,at_received AS received,at_receivable AS receivable,at_manager AS manager,");
		sql.append(" sp_name AS shop_name");
		sql.append(" FROM t_sort_allot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "allot"));
		sql.append(" UNION ALL");
		sql.append(" SELECT fe_id AS id,fe_date AS make_date,fe_number AS number,3 AS type,fe_shop_code AS shop_code,");
		sql.append(" fe_pay_state AS pay_state,fe_money AS money,fe_discount_money AS discount_money,");
		sql.append(" fe_prepay AS prepay,fe_received AS received,fe_receivable AS receivable,fe_manager AS manager,");
		sql.append(" sp_name AS shop_name");
		sql.append(" FROM t_sort_fee t");
		sql.append(" JOIN t_base_shop sp ON sp_code = fe_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "fee"));
		sql.append(" UNION ALL");
		sql.append(" SELECT pp_id AS id,pp_date AS make_date,pp_number AS number,IF (pp_money > 0, 4, 5) AS type,pp_shop_code AS shop_code,");
		sql.append(" 2 AS pay_state,0 AS money,0 AS discount_money,0 AS prepay,pp_money AS received,0 AS receivable,pp_manager AS manager,");
		sql.append(" sp_name AS shop_name");
		sql.append(" FROM t_sort_prepay t");
		sql.append(" JOIN t_base_shop sp ON sp_code = pp_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "prepay"));
		sql.append(" )temp ");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else{
			sql.append(" ORDER BY make_date DESC ");
		}
		sql.append(" LIMIT :start,:end");
		
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ShopMoneyDetailsDto.class));
	}

	@Override
	public List<String> queryShopCode(Map<String, Object> params) {
		Object shop_type = params.get("shop_type");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select sp_code ");
		sql.append(" from t_base_shop s");
		sql.append(" where 1=1 ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(" and s.sp_upcode=:shop_code");
		}else{
			sql.append(" and s.sp_code=:shop_code");
		}
		sql.append(" and s.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
}
